package itsm.isperp.module.utils;

import itsm.isperp.framework.data.dialect.DialectFactory;
import itsm.isperp.framework.utils.DateUtils;
import itsm.isperp.report.Counter;

import java.util.Calendar;
import java.util.Date;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang3.StringUtils;
import org.springframework.data.jdbc.support.DatabaseType;

/**
 * 
 * 
 * 
 * @author lizx
 * @date 2015-11-17
 * @since 1.0
 * 
 */
public class AnalysisQueryUtils {

	public static void buildAnalysisQuery(int type, StringBuilder sb,
			HttpServletRequest req) {

		String dtr1 = req.getParameter("dtr1");
		String dtr2 = req.getParameter("dtr2");
		String dtr3 = req.getParameter("dtr3");
		String dtr4 = req.getParameter("dtr4");
		String dtr5 = req.getParameter("dtr5");
		String dtr6 = req.getParameter("dtr6");
		String dtr7 = req.getParameter("dtr7");
		String dtr8 = req.getParameter("dtr8");

		String dscl1 = req.getParameter("dscl1");
		String dscl2 = req.getParameter("dscl2");
		String dscl3 = req.getParameter("dscl3");
		String dscl9 = req.getParameter("dscl9");

		Calendar cal = Calendar.getInstance();
		if (StringUtils.isNotEmpty(dtr1)) {
			String sd, ed;
			switch (dtr1) {

			case "1":
				Date d = new Date();
				sd = DateUtils.formatDate(d);
				ed = sd;

				break;
			case "2":

				cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
				sd = DateUtils.formatDate(cal.getTime());

				cal.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);
				cal.add(Calendar.DAY_OF_WEEK, 7);

				ed = DateUtils.formatDate(cal.getTime());

				break;
			case "3":

				cal.set(Calendar.DAY_OF_MONTH, 1);
				sd = DateUtils.formatDate(cal.getTime());

				cal.set(Calendar.DAY_OF_MONTH,
						cal.getActualMaximum(Calendar.DAY_OF_MONTH));

				ed = DateUtils.formatDate(cal.getTime());

				break;
			default:
			  sd=dtr1;
			  ed = dtr2;
				break;

			}

			if (DialectFactory.getDatabaseType() == DatabaseType.ORACLE) {
				sb.append("  AND (create_date  BETWEEN to_date('" + sd
						+ "','yyyy-MM-dd') AND  to_date('" + ed
						+ " 23:59','yyyy-MM-dd hh24:mi')) ");
			} else {
				sb.append("  AND (create_date  BETWEEN '" + sd + "'  AND  '"
						+ ed + " 23:59') ");
			}

		}

		String supporterName = "", incident_type = "";

		if (type == 1) {
			supporterName = "supporter_name";
			incident_type = "incident_type";
		} else if (type == 2) {
			supporterName = "plan_principal_name";
			incident_type = "task_type";
		}else if(type==3)
		{
			supporterName = "executer_name";
			incident_type = "change_type";
		}

		// 二线
		if (StringUtils.isNotEmpty(dtr3)) {

			if (Counter.LABEL_NULL.equals(dtr3)) {
				sb.append("  AND  " + supporterName + "  IS NULL");
			} else {
				sb.append("  AND " + supporterName + " IN " + toInString(dtr3));
			}
		}

		// 性质
		if (StringUtils.isNotEmpty(dtr4)) {
			if (Counter.LABEL_NULL.equals(dtr4)) {
				sb.append("  AND " + incident_type + "  IS NULL");
			} else {
				sb.append("  AND  " + incident_type + " IN " + toInString(dtr4));
			}
		}

		// 级别
		
		String dtr5column="priority_name";
		if(type==3)
		{
			dtr5column="risk_rank";
		}
		
		if (StringUtils.isNotEmpty(dtr5)) {
			if (Counter.LABEL_NULL.equals(dtr5)) {
				sb.append("  AND "+dtr5column+"  IS NULL");
			} else {
				sb.append("  AND "+dtr5column+"  IN " + toInString(dtr5));
			}
		}

		// 状态
		if (StringUtils.isNotEmpty(dtr6)) {
			if (Counter.LABEL_NULL.equals(dtr6)) {
				sb.append("  AND   status IS NULL'");
			} else {
				sb.append("  AND   status  IN " + toInString(dtr6));
			}
		}

		String slacolumn="sla_status";
		if(type==3)
		{
			slacolumn="evaluation_result";
		}
		
		if (StringUtils.isNotEmpty(dtr8)) {
			if (Counter.LABEL_NULL.equals(dtr8)) {
				sb.append("  AND cus_user_name IS NULL");
			} else {
				sb.append("  AND cus_user_name IN " + toInString(dtr8));
			}
		}
		
		if (StringUtils.isNotEmpty(dtr7)) {
      if (Counter.LABEL_NULL.equals(dtr7)) {
        sb.append("  AND "+slacolumn+" IS NULL");
      } else {
        sb.append("  AND "+slacolumn+" IN " + toInString(dtr7));
      }
    }

		// 担当
		if (StringUtils.isNotEmpty(dscl1)) {

			sb.append(" AND confirmtimeman_name IN (SELECT u.fullname FROM app_user u INNER JOIN app_department b ON u.app_department_id=b.id WHERE b.treenode_path LIKE '%"
					+ dscl1 + "%')");

		} else if (StringUtils.isNotEmpty(dscl2)) {
			// 供应商
			sb.append("  AND "
					+ supporterName
					+ " IN (SELECT u.fullname FROM app_user u INNER JOIN app_department b ON u.app_department_id=b.id WHERE b.treenode_path LIKE '%"
					+ dscl2 + "%')");

		} else if (StringUtils.isNotEmpty(dscl3)) {
			// 系统；
			if (type == 1) {

				sb.append("  AND icd_category_id IN (SELECT id FROM icd_category c  WHERE c.treenode_path LIKE '%"
						+ dscl3 + "%')");
			} else if (type == 2) {

				sb.append("  AND task_category_id IN (SELECT id FROM icd_category c  WHERE c.treenode_path LIKE '%"
						+ dscl3 + "%')");
			}
			else if (type == 3) {

				sb.append("  AND chg_category_id IN (SELECT id FROM chg_category c  WHERE c.treenode_path LIKE '%"
						+ dscl3 + "%')");
			}
		}
		else if (StringUtils.isNotEmpty(dscl9)) {
      // 系统；
		  sb.append("  AND cus_user_id"
          + " IN (SELECT u.id FROM app_user u INNER JOIN app_department b ON u.app_department_id=b.id WHERE b.treenode_path LIKE '%"
          + dscl9 + "%')");
    }
	}

	public static String toInString(String str) {

		String[] s = str.split(",");
		String res = "('" + s[0] + "'";

		for (int i = 0; i < s.length; i++) {
			res += ",'" + s[i] + "'";
		}

		return res + ")";
	}

}
